package com.kehong.jdbcdemo;

import java.sql.*;
import java.util.TreeSet;

/**
 * @author zhang peng yang ming
 * @CopyRight (C) http://www.csdn.com
 * @brief jdbc 初级编程
 * @email 122162005@qq.com
 * @date 2018-07-10
 */
public class Jdbc {
    /**数据库连接地址*/
    private static final String url = "jdbc:mysql://localhost:3306/station";
    /**数据库账户*/
    private static final String user = "root";
    /**数据库连接密码*/
    private static final String password = "root";

    private static final String sql = "SELECT * FROM product WHERE id=2";
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        System.out.println(login("zhangsan02", "123456' OR '1'='1"));
    }

    public static void select() throws ClassNotFoundException, SQLException {
        /**
         * 加载驱动，建立链接
         * 创建语句对象
         * 执行SQL
         * 处理结果集
         * 关闭链接
         */
        // 1
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection(url, user, password);
        // 创建语句对象
        Statement statement = connection.createStatement();
        // 执行SQL
        ResultSet rs = statement.executeQuery(sql);  // CURD
        // 处理结果集
        while (rs.next()) {
            // 获取 表记录中ID
            System.out.println("id: " + rs.getInt("id"));
            System.out.println("classify_id: " + rs.getInt("classify_id"));
            System.out.println("product_name: " + rs.getString("product_name"));
            System.out.println("sale_price : " + rs.getDouble("sale_price"));

            System.out.println("cutoff : " + rs.getDouble(7));  // 0.9
            System.out.println("cost_price : " + rs.getDouble(8));  // 33
//            rs.getObject(1);
        }

        // 关闭链接
        statement.close();
        connection.close();
    }

    /**
     * 根据 指定的 id 删除数据库表中的记录
     * @return
     */
    public static boolean delete(String id) throws ClassNotFoundException, SQLException {
        /**
         * 加载驱动，建立链接
         * 创建语句对象
         * 执行SQL
         * 处理结果集
         * 关闭链接
         */
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection(url, user, password);
        // 创建语句对象
        Statement statement = connection.createStatement();
        // 执行SQL
        String sql = "DELETE FROM product WHERE id=" + id;

//        boolean flag = statement.execute(sql);
        int r = statement.executeUpdate(sql);

        // 关闭链接
        statement.close();
        connection.close();
        return r > 0;
    }


    /**
     * 根据 指定的 id 删除数据库表中的记录
     * @return
     */
    public static boolean update(String columnLabel, String updateContent, int id) throws ClassNotFoundException, SQLException {
        /**
         * 加载驱动，建立链接
         * 创建语句对象
         * 执行SQL
         * 处理结果集
         * 关闭链接
         */
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection(url, user, password);
        // 创建语句对象
        Statement statement = connection.createStatement();
        // 执行SQL
        String sql = "UPDATE product SET "+ columnLabel +"='" + updateContent +"' WHERE id=" + id;

//        boolean flag = statement.execute(sql);
        int r = statement.executeUpdate(sql);

        // 关闭链接
        statement.close();
        connection.close();
        return r > 0;
    }

    /**
     * 插入数据
     * @return
     */
    public static boolean insert() throws ClassNotFoundException, SQLException {
        /**
         * 加载驱动，建立链接
         * 创建语句对象
         * 执行SQL
         * 处理结果集
         * 关闭链接
         */
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager.getConnection(url, user, password);
        // 创建语句对象
        Statement statement = connection.createStatement();
        // 执行SQL
        String sql = "INSERT INTO product (product_name, classify_id, sale_price, supplier, brand, cutoff) " +
                "VALUES('华硕电脑', 4, 1999, '华硕', '华硕', 0.9)";

//        boolean flag = statement.execute(sql);
        int r = statement.executeUpdate(sql);

        // 关闭链接
        statement.close();
        connection.close();
        return r > 0;
    }


    /**
     * 用户登录
     * 通过用户名称和用户密码在数据库查询该用户是否存在
     * @param username 用户名
     * @param passwd 密码
     * @return
     */
    public static boolean login(String username, String passwd) throws ClassNotFoundException, SQLException {
        /**
         * 加载驱动，建立链接
         * 创建语句对象
         * 执行SQL
         * 处理结果集
         * 关闭链接
         */
        Class.forName("com.mysql.jdbc.Driver");
        // 建立链接
        Connection connection = DriverManager.getConnection(url, user, password);
        //  创建语句对象
        String queryUserSql = "SELECT * FROM product WHERE product_name=? AND brand=?";

        /////////////       预编译开始        /////////////  sql 中要有占位符
        PreparedStatement statement = connection.prepareStatement(queryUserSql);
        statement.setString(1, username);
        statement.setString(2, passwd);
        /////////////       预编译结束        /////////////
        // 执行SQL
        ResultSet rs = statement.executeQuery();

        // 处理结果集
        boolean loginSuccess = false;  // 是否登录成功
        if (rs.next()) {
            loginSuccess = true;
        }

        // 关闭链接
        rs.close();
        statement.close();
        connection.close();
        return loginSuccess;
    }
}
